package com.dhcc.DRGsHosp.application.impl;

import com.dhcc.DRGsHosp.application.DrgsIndexCalApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.facade.dto.IndexCalDTO;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.GlobalData;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.domain.SqlQuery;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.openkoala.security.shiro.CurrentUser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.transaction.annotation.Transactional;

import javax.inject.Named;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.*;

/**
 * 
 * @ClassName: DrgsIndexCalApplicationImpl
 * @Description ： DRGs指标计算总方法
 * @Date: 2017年5月21日 上午9:44:34
 * @Author LiuHongJun
 * @Version 2.5.0 DHC-DRGs
 */
@Named("drgsIndexCalApplicationImpl")
@Transactional
public class DrgsIndexCalApplicationImpl implements DrgsIndexCalApplication {
	private EntityRepository repository;
	private static final Logger logger = LoggerFactory.getLogger(DrgsIndexCalApplicationImpl.class);

	
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	@Autowired
	private NamedParameterJdbcTemplate jdbcTempSeverice;

	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(EntityRepository.class, "repository");
		}
		return repository;
	}

	/**
	 * 
	 * getRecentlyManageYear.
	 * 
	 * @Description：管理标杆年份
	 * @Author LiuHongjun
	 * @return
	 * @Date:2017年5月19日上午11:51:39
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getRecentlyManageYear' + #mrDate +#msCode + #monitorType  ")
	public String getRecentlyManageYear(String mrDate, String msCode,String monitorType) {
		String manage = mrDate;
		String sql = "";
		if (msCode != null && msCode.contains("manager_")) {//管理目标，全院、科室组、科室等
			String filterSql="";
			if ("depGroup".equals(monitorType)) {
				filterSql+=" where t.PARDEPGROUP_CODE is not null \n";
				filterSql+=" and t.PARDEP_CODE is null \n";
				filterSql+=" and t.PARDRG_CODE is null \n";
			} else if ("dep".equals(monitorType)) {
				filterSql+=" where t.PARDEPGROUP_CODE is  null \n";
				filterSql+=" and t.PARDEP_CODE is not null \n";
				filterSql+=" and t.PARDRG_CODE is null \n ";
			 //病区和医师取得是他所在科室的绩效，必须为科室表和医师表配置对应的科室代码
			}  else if ("ward".equals(monitorType)) {
				filterSql+=" where t.PARDEPGROUP_CODE is  null \n";
				filterSql+=" and t.PARDEP_CODE is not null \n";
				filterSql+=" and t.PARDRG_CODE is null";
			}else if (StringUtils.hasLength(monitorType)&&monitorType.matches("(protomedicus)|(clinic)|(physician)|(resident)")) {
				filterSql+=" where t.PARDEPGROUP_CODE is  null \n";
				filterSql+=" and t.PARDEP_CODE is not null \n";
				filterSql+=" and t.PARDRG_CODE is null \n";
			}  else if ("drgs".equals(monitorType)) {
				filterSql+=" where t.PARDRG_CODE is not null \n";
				filterSql+=" and t.PARDEPGROUP_CODE is null \n";
				filterSql+=" and t.PARDEP_CODE is null \n";
			} else {
				filterSql+=" where t.PARDEPGROUP_CODE is null \n";
				filterSql+=" and t.PARDEP_CODE is null \n";
				filterSql+=" and t.PARDRG_CODE is null \n";
			}
			sql = "SELECT max(t.manage_year) year FROM drgs.t_quotamanage t  "+filterSql+" and t.manage_year='" + mrDate
					+ "' and t.par_ms_code like '" + msCode + "%' ";
			String year = jdbcTemplateObj.queryForObject(sql, String.class);
			if (year==null) {
				sql = "SELECT max(t.manage_year) year FROM drgs.t_quotamanage t  "+filterSql+" and t.par_ms_code like '" + msCode+ "%' ";
				year = jdbcTemplateObj.queryForObject(sql, String.class);
			}
			if (year!=null) {
				manage = year;
			}
		} else if (msCode != null && "std_manager".equals(msCode)) {//病组管理目标
			sql = "  SELECT max(t.manage_year) as year FROM DRGS.t_Quotamanage t \r\n" + 
					"WHERE t.pardep_code is null and t.pardepgroup_code is null \r\n" + 
					"and t.parquota_code is not null and t.pardrg_code is not null\r\n" + 
					"and t.par_ms_code='manager_inner' and t.manage_year='"+mrDate+"' ";
			String year = jdbcTemplateObj.queryForObject(sql, String.class);
			if (year==null) {
				sql = 	"SELECT max(t.manage_year) FROM DRGS.t_Quotamanage t \r\n" + 
						"WHERE t.pardep_code is null and t.pardepgroup_code is null \r\n" + 
						"and t.parquota_code is not null and t.pardrg_code is not null\r\n" + 
						"and t.par_ms_code='manager_inner' ";
				year = jdbcTemplateObj.queryForObject(sql, String.class);
			}
			if (year!=null) {
				manage = year;
			}
		}else if (msCode != null && msCode.contains("std_")) {
			sql = "SELECT max(d.std_year) as year FROM drgs.t_managesummarydtl d WHERE d.std_year='" + mrDate
					+ "' and d.par_ms_code='" + msCode + "' ";
			String year = jdbcTemplateObj.queryForObject(sql, String.class);
			if (year==null) {
				sql = "SELECT max(d.std_year) as year FROM drgs.t_managesummarydtl d WHERE  d.par_ms_code='" + msCode+ "'";
				year = jdbcTemplateObj.queryForObject(sql, String.class);
			}
			if (year!=null) {
				manage = year;
			}
		}
		
		return manage;
	}

	/**
	 * 
	 * @Description：根据多指标代码quota_id从T_QUOTASELECTDTL 表中取出真正的指标代码
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getQuotasByMoreCode(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getQuotasByMoreCode' + #quota_id")
	public List<Map<String, Object>> getQuotasByMoreCode(String quota_id) {
		String sql = "select b.quota_code, b.unit,b.text,b.select_sql from DRGs.t_quotaselectdtl a \n "
				+ "left join drgs.t_quotaselect b on a.quota_code=b.quota_code \n" + "where quota_id='" + quota_id
				+ "' order by indexes ";
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql);
		return resultList;
	}

	/**
	 * 
	 * @Description：获取指标小数点精度
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getUnitByQuotaCode(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getUnitByQuotaCode' + #quotaCode")
	public String getUnitByQuotaCode(String quotaCode) {
		String sql = "select unit from DRGs.t_quotaselect where quota_code='" + quotaCode + "' ";
		String unit =jdbcTemplateObj.queryForObject(sql, String.class);
		if (unit==null) {
			unit="0";
		}
		return unit;
	}

	/**
	 * @Description：计算指标（环比，同比） @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getDrgsIndex(com.dhcc.DRGsHosp.core.domain.index,
	 *      java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getDrgsIndex' + #index.pType + #index.isHistory + "
			+ "#index.monitorType  + #index.dateFrom + #index.dateTo + #index.parwardCode  + #index.compareType  +"
			+ "#index.stdId + #index.verRegion + #index.pardepgroupCode + #index.pardepCode + #index.pargroupName + #index.protomedicus + "
			+ "#index.physician + #index.resident + #index.parmdcCode + #index.paradrgCode + #index.pardrgCode + #index.deps + #index.roleName + "
			+ "#index.isKeyDisGroup + #index.isDifDisGroup + #index.groupType + #index.type1 +#index.msCode  + #index.verCode + #index.hospCode +"
			+ "#index.scoreConfig +#index.groupByTime + #index.mdcs + #index.userCode +#quotaCode")
	public List<Object[]> getDrgsIndex(IndexCal index, String quotaCode) {
		String pType = index.getpType();
		String dateFrom = index.getDateFrom();
		String dateTo = index.getDateTo();
		String manageYear = index.getManageYear();
		String sqlStr = index.getSqlStr();
		String stdId = index.getStdId();
		String msCode = index.getMsCode();
		String verRegion=index.getVerRegion();
		String verCode=index.getVerCode();
		Map<String, Object> map = new HashMap<>();
		try {
			if (!"".equals(sqlStr)) {
				if (sqlStr.contains(":dateFrom")) {
					map.put("dateFrom", dateFrom);
				}
				if (sqlStr.contains(":dateTo")) {
					map.put("dateTo", dateTo);
				}
				if (sqlStr.contains(":manageYear")) {
					map.put("manageYear", manageYear);
				}
				if (sqlStr.contains(":stdId")) {
					map.put("stdId", stdId);
				}
				if (sqlStr.contains(":pType")) {
					map.put("pType", pType);
				}
				if (sqlStr.contains(":ms_code")) {
					map.put("ms_code", msCode);
				}
				if (sqlStr.contains(":ver_region")) {
					map.put("ver_region", verRegion);
				}
				if (sqlStr.contains(":verCode")) {
					map.put("verCode", verCode);
				}
				// 外地高权重指标计算，从配置表获取本地邮政编码前三位
				if (sqlStr.contains(":post_code")) {
					String hiPostCode = jdbcTemplateObj.queryForObject(
							"SELECT a.cfg_value FROM drgs.t_config a WHERE a.cfg_code='HIW_POSTCODE' ", String.class);
					map.put("post_code", hiPostCode);
				}
				if (sqlStr.contains(":autibiotic")) {
					String autibiotic = jdbcTemplateObj.queryForObject("select t.cost_sub_code from drgs.t_costsub t where t.cost_sub_desc like '%抗菌%'", String.class);
					map.put("autibiotic", autibiotic);
				}
				// 根据比较对象替换sql中的order by group by
				DrgsIndex.replaceSqlByMonitorType(index,quotaCode);
				// 条件拼接
				DrgsIndex.createSearch(index, map);
				sqlStr = index.getSqlStr();
			}
			SqlQuery sqlQuery = getRepository().createSqlQuery(sqlStr);
			if (!map.isEmpty()) {
				sqlQuery = sqlQuery.setParameters(map);
			}
			List<Object[]> resultList = sqlQuery.list();
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			// 将错误指标代码和sql和错误信息写入文件
			if (sqlStr == null || "".equals(sqlStr) || "null".equals(sqlStr)) {
				sqlStr = "指标SQL不存在";
			}
			logger.error("异常：" + e.getMessage() + "\n" + sqlStr);
			return new LinkedList<>();
		}
	}

	/**
	 * @Description：标杆比
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getDrgsInnerManagerIndex(com.dhcc.DRGsHosp.core.domain.index)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getDrgsStdIndex' + #quotaCode + #index.monitorType  + #index.dateFrom + #index.stdId +"
			+ " #index.dateTo + #index.manageYear + #index.pardepgroupCode + #index.pardepCode + #index.pargroupName + "
			+ "#index.verRegion + #index.parmdcCode + #index.paradrgCode + #index.pardrgCode + #index.pType +"
			+ "#index.hospCode + #index.msCode+ #index.physician + #index.resident +#index.protomedicus + #index.userCode +#quotaCode")
	public List<Object[]> getDrgsStdIndex(IndexCal index, String quotaCode) {
		String dateFrom = index.getDateFrom();
		String dateTo = index.getDateTo();
		String stdYear = index.getManageYear();
		String msCode = index.getMsCode();
		String verRegion=index.getVerRegion();
		String asStdid=index.getStdId();
		Map<String, Object> map = new HashMap<>();
		String sqlStr = index.getSqlStr();
		try {
			if(!"".equals(sqlStr)) {
				String quotaInfo=GlobalData.stdQuotaInfo.get(quotaCode);
				/*说明指标不能反推*/
				if(quotaInfo==null) {
					return new LinkedList<>();
				}
	            String [] quotaInfoArray=quotaInfo.split(",");
                String quotaType = quotaInfoArray[0];//指标类型
                String stdQuota = quotaInfoArray[1];//年度标杆表字段
                String manageQuota=quotaInfoArray[2];//管理目标表字段，考虑到管理目标表中，死亡人数需要用到死亡率来推算，后期方便拓展
				// 根据mscode来替换所需要比较的对象
				DrgsIndex.replaceStdSqlByMscode(index,quotaCode,quotaType,stdQuota);
				// 根据分析的对象替换用来group by order by的字段,使用对象传递
				DrgsIndex.replaceSqlByMonitorType(index,quotaCode);
				sqlStr=index.getSqlStr();
				if ("std_computer".equals(msCode)) {
					if (sqlStr.contains(":dateFrom")) {
						map.put("dateFrom", dateFrom);
					}
					if (sqlStr.contains(":dateTo")) {
						map.put("dateTo", dateTo);
					}
					//标杆定位
					if (sqlStr.contains(":stdId")) {
						map.put("stdId", asStdid);
					}
					//分组方案
					if (sqlStr.contains(":ver_region")) {
						map.put("ver_region", verRegion);
					}				
				}else if("std_manager".equals(msCode)){
					if (sqlStr.contains(":dateFrom")) {
						map.put("dateFrom", dateFrom);
					}
					if (sqlStr.contains(":dateTo")) {
						map.put("dateTo", dateTo);
					}
					if(sqlStr.contains(":manageYear")) {
						map.put("manageYear", stdYear);
					}
					if(sqlStr.contains(":quotaCode")) {
						map.put("quotaCode", "-".equals(manageQuota)?quotaCode:manageQuota);
					}
					//分组方案
					if (sqlStr.contains(":ver_region")) {
						map.put("ver_region", verRegion);
					}
				}else {
					if (sqlStr.contains(":dateFrom")) {
						map.put("dateFrom", dateFrom);
					}
					if (sqlStr.contains(":dateTo")) {
						map.put("dateTo", dateTo);
					}
					if (sqlStr.contains(":stdYear")) {
						map.put("stdYear", stdYear);
					}
					// 指标代码
					if (sqlStr.contains(":quotaCode")) {
						map.put("quotaCode", "-".equals(manageQuota)?quotaCode:manageQuota);
					}
					// 标杆比类型
					if (sqlStr.contains(":ms_code")) {
						map.put("ms_code", msCode);
					}
					//分组方案
					if (sqlStr.contains(":ver_region")) {
						map.put("ver_region", verRegion);
					}
				}
				// 条件拼接
				DrgsIndex.createSearch(index, map);
				sqlStr=index.getSqlStr();
			}
			SqlQuery sqlQuery = getRepository().createSqlQuery(sqlStr);
			if (!map.isEmpty()) {
				sqlQuery = sqlQuery.setParameters(map);
			}
			List<Object[]> resultList = sqlQuery.list();
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			// 将错误指标代码和sql和错误信息写入文件
			if (sqlStr == null || "".equals(sqlStr) || "null".equals(sqlStr)) {
				sqlStr = "指标SQL不存在";
			}
			logger.error("异常：" + e.getMessage() + "\n" + sqlStr);
			return new LinkedList<>();
		}
	}

	/**
	 * 
	 * @Description：获取系统管理目标（提前维护好的各个对象的各标杆值，不用drg的标杆计算） @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getDrgsManagerIndex(com.dhcc.DRGsHosp.core.domain.index)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getDrgsManagerIndex' + #index.monitorType + #quotaCode + #index.pardepgroupCode +"
			+ "#index.pardepCode +  #index.hospCode +#index.pardrgCode +#index.msCode +#index.manageYear +#index.dateFrom +"
			+ "#index.dateTo +#index.deps +#quotaCode")
	public List<Object[]> getDrgsManagerIndex(IndexCal index, String quotaCode) {
		String depGroupCode = index.getPardepgroupCode();
		String depCode = index.getPardepCode();
		String monitorType = index.getMonitorType();
		String msCode = index.getMsCode();// 外部标杆和内置标杆
		String manageYear = index.getManageYear();
		String deps=index.getDeps();//获取多科室
		Map<String, Object> map = new HashMap<>();
		String sqlStr = index.getSqlStr();
		try {
			if (!"".equals(sqlStr)) {
				// 标杆年份
				if (sqlStr.contains(":manageYear")) {
					map.put("manageYear", manageYear);
				}
				// 指标代码
				if (sqlStr.contains(":quotaCode")) {
					map.put("quotaCode", quotaCode);
				}
				// 绩效比类型
				if (sqlStr.contains(":ms_code")) {
					map.put("ms_code", msCode);
				}
				sqlStr = index.getSqlStr();
				/*根据分析的对象进行替换*/
				if ("depGroup".equals(monitorType)) {
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is not null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", " t.PARDEPGROUP_CODE ccode");
				} else if ("dep".equals(monitorType)) {
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is  null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is not null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", " t.PARDEP_CODE ccode");
				 //病区和医师取得是他所在科室的绩效，必须为科室表和医师表配置对应的科室代码
				}  else if ("ward".equals(monitorType)) {
					sqlStr = sqlStr.replaceAll("drgs.t_quotamanage t", "drgs.t_quotamanage t \n left join drgs.t_ward ward on t.pardep_code=ward.pardep_code \n ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is  null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is not null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", " ward.ward_code ccode");
				}else if (StringUtils.hasLength(monitorType)&&monitorType.matches("(protomedicus)|(clinic)|(physician)|(resident)")) {
					sqlStr = sqlStr.replaceAll("drgs.t_quotamanage t", "drgs.t_quotamanage t \n left join drgs.t_clinicgroup doc on instr(doc.pardep_code,t.pardep_code)>0 \n ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is  null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is not null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", " doc.group_code ccode");
					if(StringUtils.hasLength(depCode)) {
						sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE=:depCode and  1=1 ");
						map.put("depCode", depCode);
					}
				}  else if ("drgs".equals(monitorType)) {
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is not null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", " t.PARDRG_CODE ccode");
					if (!StringUtils.hasLength(depGroupCode) ) {
						sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is null and  1=1 ");
					}
					if ( !StringUtils.hasLength(depCode)) {
						sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is null and  1=1 ");
					}
				} else {
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEPGROUP_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDEP_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll(" 1=1 ", " t.PARDRG_CODE is null and  1=1 ");
					sqlStr = sqlStr.replaceAll("-obj-", "1 ccode");
				}
				//多科室过滤
				if(StringUtils.hasLength(deps)){
					sqlStr=DrgsIndex.setDepsSql(deps,sqlStr,"t.PARDEP_CODE");
				}
			}
			SqlQuery sqlQuery = getRepository().createSqlQuery(sqlStr);
			if (!map.isEmpty()) {
				sqlQuery = sqlQuery.setParameters(map);
			}
			List<Object[]> resultList = sqlQuery.list();
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			// 将错误指标代码和sql和错误信息写入文件
			if (sqlStr == null || "".equals(sqlStr) || "null".equals(sqlStr)) {
				sqlStr = "指标SQL不存在";
			}
			logger.error("异常：" + e.getMessage() + "\n" + sqlStr);
			return new LinkedList<>();
		}
	}

	/**
	 * 
	 * getIndexSql.
	 * 
	 * @Description：根据指标代码获取计算的sql
	 * @Author LiuHongjun
	 * @param quotaCode
	 * @return
	 * @throws SQLException
	 * @Date:2017年5月23日上午10:24:11
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getSqlByQuotaCode' + #quotaCode ")
	public String getSqlByQuotaCode(String quotaCode) {
		String sqlStr = "";
		String sql = " select select_sql from DRGs.t_quotaselect where quota_code='" + quotaCode + "' ";
		List<Object> sqlList = getRepository().createSqlQuery(sql.toString()).list();
		if (sqlList.size() > 0) {
			Clob clob = (Clob) sqlList.get(0);
			try {
				sqlStr = DrgsIndex.ClobToString(clob);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sqlStr;
	}

	/**
	 * 
	 * @Title: findCaseCountFast
	 * @author:LiQingYang
	 * @Description: 获取病例总数
	 * @param: @param index
	 * @param: @param isGroup
	 * @param: @param isGt60
	 * @param: @param outWay
	 * @param: @param risk
	 * @param: @return
	 * @return: int
	 * @throws @date:2017年6月26日
	 *             上午10:31:02
	 */
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'caseCount' + "
			+ "'isGroup' + #isGroup + 'isGt60' + #isGt60 + 'outWay'+ #outWay + 'risk'+ #risk  + "
			+ "#index.pType + #index.pardepgroupCode + #index.deps + #index.pardepCode + #index.parwardCode  + "
			+ "#index.pargroupName + #index.protomedicus + #index.resident + #index.physician + "
			+ "#index.parmdcCode + #index.paradrgCode + #index.pardrgCode  + #index.typeOne  + "
			+ "#index.dateFrom + #index.dateTo + #index.stdId + #index.manageYear + #index.typeTwo  +"
			+ "#index.type2 + #index.quotaCode + #index.msCode+ #index.mdcs + #index.hospCode ")
	public int findCaseCountFast(IndexCal index, String isGroup, String isGt60, String outWay, String risk) {
		// 获取执行sql
		String caseCountSql = getCaseSql(index, isGroup, isGt60, outWay, risk, "caseCount");
		String caseCountHead = "SELECT COUNT(*) caseCount FROM ( ";
		String caseCountFoot = " ) ";
		return jdbcTemplateObj.queryForObject(caseCountHead + caseCountSql + caseCountFoot, Integer.class);
	}

	/**
	 * 
	 * @Title: findCaseListFast
	 * @author:LiQingYang
	 * @Description: 获取病例方法重写，速度提升至少一倍
	 * @param: @param page 页数
	 * @param: @param pageSize 每页展示条数
	 * @param: @param index 参数对象
	 * @param: @param isGroup 是否入组病例,0:否,1:是,空串:全部
	 * @param: @param isGt60 是否大于60天病例,0:否,1:是
	 * @param: @param outWay 离院方式
	 * @param: @param risk 病组风险等级
	 * @param: @return
	 * @return: List<Map<String, Object>>
	 * @throws @date:2017年6月20日
	 *             上午10:36:01
	 */
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'caseDetail' + #page + #pageSize +#orderColumn+ #orderMethod+ "
			+ "'isGroup' + #isGroup + 'isGt60' + #isGt60 + 'outWay' + #outWay + 'risk' + #risk  + "
			+ "#index.pType + #index.pardepgroupCode + #index.deps + #index.pardepCode + #index.parwardCode  + "
			+ "#index.pargroupName + #index.protomedicus + #index.resident + #index.physician + "
			+ "#index.parmdcCode + #index.paradrgCode + #index.pardrgCode  + #index.typeOne  +"
			+ "#index.dateFrom + #index.dateTo + #index.stdId + #index.manageYear + #index.typeTwo  + "
			+ "#index.type2 + #index.quotaCode + #index.msCode+ #index.mdcs + #index.hospCode ")
	public List<Map<String, Object>> findCaseListFast(int page, int pageSize, IndexCal index, String isGroup,
			String isGt60, String outWay, String risk, String orderColumn, String orderMethod) {
		// 不分页,每页默认99999999
		if (pageSize == -1) {
			pageSize = 99999999;
		}
		// 获取执行sql
		String caseListSql = getCaseSql(index, isGroup, isGt60, outWay, risk, "caseList");

		String pageSqlHead = "SELECT * FROM (SELECT PAGERESULT.*, ROWNUM RN FROM (";

		String pageSqlFoot = " ORDER BY " + orderColumn + " " + orderMethod + ") PAGERESULT ) WHERE RN > " + page
				+ " AND RN <= " + (page + pageSize);

		List<Map<String, Object>> mrListMaps = jdbcTemplateObj.queryForList(pageSqlHead + caseListSql + pageSqlFoot);

		return mrListMaps;
	}

	/**
	 * 
	 * @Title: getCaseSql
	 * @author:LiQingYang
	 * @Description: 获取病例执行sql
	 * @param: @param
	 *             index
	 * @param: @param
	 *             isGroup
	 * @param: @param
	 *             isGt60
	 * @param: @param
	 *             outWay
	 * @param: @param
	 *             risk
	 * @param: @param
	 *             sqlType:获取sql类型。caseCount、caseList
	 * @param: @return
	 * @return: String
	 * @throws @date:2017年6月26日
	 *             上午10:41:35
	 */
	private String getCaseSql(IndexCal index, String isGroup, String isGt60, String outWay, String risk,
			String sqlType) {
        String hospCode=index.getHospCode(); //医院代码
		String pType = index.getpType();// 病人类型

		String depGroupCode = index.getPardepgroupCode();// 科室组编码
		String depCode = index.getPardepCode();// 科室编码
		String deps = index.getDeps();// 多科室编码
		String wardCode = index.getParwardCode();// 病区编码

		String groupName = index.getPargroupName();// 主诊医师
		String protomedicus = index.getProtomedicus();// 主任医师
		String physician = index.getPhysician();// 主治医师
		String resident = index.getResident();// 住院医师

		String mdcCode = index.getParmdcCode();// MDC编码
		String adrgCode = index.getParadrgCode();// ADRG编码
		String drgCode = index.getPardrgCode();// DRG编码

		String dateFrom = index.getDateFrom();// 开始时间
		String dateTo = index.getDateTo();// 结束时间

		String compareType = index.getCompareType();// 比较类型 同比 环比 院内均值 标杆比 绩效比

		String stdId = index.getStdId();// 参与计算的标杆年份
		String verRegion = index.getVerRegion();//获取分组方案
		String manageYear = index.getManageYear();// 绩效标杆年份

		String type2 = index.getType2();// 病例明细搜索条件

		String quotaCode = index.getQuotaCode();// 指标代码

		String outManageCode = index.getMsCode();// 比较类型

		String typeOne = index.getTypeOne();// 滑块值
		String typeTwo = index.getTypeTwo();// 费用超标类型

		/**
		 * 查询病例基本信息和住院信息,关联表：MR_BASEINFO、MR_IPINFO
		 */
		StringBuilder mrBaseStr = new StringBuilder("");
		mrBaseStr.append(
				"SELECT BASE.PMAININDEX,BASE.P00030000 HEALTH_NO ,BASE.P00040000 IP_COUNT,BASE.P00050000 MR_NO,BASE.P00060000 PATIENT_NAME,BASE.P00070001 SEX,BASE.P00090000 AGE_YEAR,BASE.P00090001 AGE_MONTH,BASE.P00090002 AGE_DAYS,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') IN_DATE,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') OUT_DATE,IP.P00300001 DEP_CODE,dep.DEP_NAME,IP.P00310000 WARD,IP.P00320000 IN_DAYS,IP.P00800000 COSTAMOUNT,IP.P00600100 DIRECTOR,IP.P00600200 PROTOMEDICUS,IP.P00600300 PHYSICIAN,IP.P00600400 RESIDENT  \n ");
		mrBaseStr.append(
				" FROM DRGS.MR_BASEINFO BASE LEFT JOIN DRGS.MR_IPINFO IP ON BASE.PMAININDEX = IP.PMAININDEX  \n ");
		mrBaseStr.append(
				" LEFT JOIN DRGS.T_DEPARTMENT DEP ON IP.P00300001=DEP.DEP_CODE   \n ");
		mrBaseStr.append(" WHERE 1 = 1 AND instr('" + hospCode + "' , BASE.p00010001)>0  AND TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN '" + dateFrom + "' AND '" + dateTo
				+ "'  \n ");

		// 离院方式
		if (StringUtils.hasLength(outWay) && ("5".equals(outWay))) {
			mrBaseStr.append(" AND IP.P00620001 = '5' \n ");
		}
		// 住院天数
		if (StringUtils.hasLength(isGt60) && ("1".equals(isGt60))) {
			mrBaseStr.append(" AND IP.P00320000 > 60 \n ");
		}
		// 住院天数--大于60天或未入组时，添加大于60天的条件
		if (StringUtils.hasLength(isGroup) && ("0".equals(isGroup))) {
			mrBaseStr.append(" AND IP.P00320000 < 61 \n ");
		}
		// 科室组
		if (StringUtils.hasLength(depGroupCode)) {
			mrBaseStr.append(
					" AND EXISTS   (SELECT 1 FROM DRGS.T_DEPARTMENT T WHERE IP.P00300001= T.DEP_CODE AND T.PARDEPGROUP_CODE = '"
							+ depGroupCode + "')  \n ");
		}

		// 科室
		if (StringUtils.hasLength(depCode)) {
			mrBaseStr.append(" AND IP.P00300001 = '" + depCode + "' \n ");
		}

		// 多科室
		if (StringUtils.hasLength(deps)) {
			String depsArr[] = deps.split(",");// 多科室
			mrBaseStr.append(" AND ( \n ");
			for (int i = 0; i < depsArr.length; i++) {
				if (i == 0) {
					mrBaseStr.append(" IP.P00300001 = '" + depsArr[i] + "' \n ");
				} else {
					mrBaseStr.append(" OR IP.P00300001 = '" + depsArr[i] + "' \n ");
				}
			}
			mrBaseStr.append(" ) \n ");
		}

		// 病区
		if (StringUtils.hasLength(wardCode)) {
			mrBaseStr.append(" AND IP.P00310001 = '" + wardCode + "' \n ");
		}

		// 主诊医师
		if (StringUtils.hasLength(groupName)) {
			mrBaseStr.append(" AND IP.P00601001 = '" + groupName + "' \n ");
		}

		// 主任医师
		if (StringUtils.hasLength(protomedicus)) {
			mrBaseStr.append(" AND IP.P00600201 = '" + protomedicus + "' \n ");
		}

		// 主治医师
		if (StringUtils.hasLength(physician)) {
			mrBaseStr.append(" AND IP.P00600301= '" + physician + "' \n ");
		}

		// 住院医师
		if (StringUtils.hasLength(resident)) {
			mrBaseStr.append(" AND IP.P00600401= '" + resident + "' \n ");
		}

		/**
		 * 获取分组信息,医保病例过滤
		 */
		StringBuilder mrGroupStrHead = new StringBuilder(" SELECT MRBASE.*,PD.PARDRG_CODE,PD.VER_CODE FROM( ");

		StringBuilder mrGroupStrFoot = new StringBuilder(" ) MRBASE ");

		// 医保病例
		if (StringUtils.hasLength(pType) && "1".equals(pType)) {
			mrGroupStrFoot.append(" INNER JOIN DRGS.T_MEDINS MD ON MRBASE.PMAININDEX = MD.PARID  \n ");
		}
		// 出院病例
		if (!StringUtils.hasLength(isGroup)) {
			mrGroupStrFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG  PD ON PD.PARID = MRBASE.PMAININDEX  and pd.ver_region='"+verRegion+"'\n ");
		} else {
			mrGroupStrFoot.append(" INNER JOIN DRGS.T_PATIENTDRG  PD ON PD.PARID = MRBASE.PMAININDEX  and pd.ver_region='"+verRegion+"' \n ");
		}

		// 是否入组
		if (StringUtils.hasLength(isGroup) && "0".equals(isGroup)) {// 未入组
			mrGroupStrFoot.append(" AND PD.PARDRG_CODE IS NULL  \n ");

		} else if (StringUtils.hasLength(isGroup) && "1".equals(isGroup)) {// 入组
			mrGroupStrFoot.append(" AND PD.PARDRG_CODE IS NOT NULL  \n ");
		}

		/**
		 * 获取标杆信息
		 */
		StringBuilder mrStdStrHead = new StringBuilder(
				" SELECT  MRGROUP.*,STD.AS_RISK_LEVEL,STD.AS_WEIGHT,STD.AS_AVG_FEE,STD.AS_AVG_DAYS,DRG.DRG_DESC,DRG.ADRG_CODE FROM (");

		StringBuilder mrStdStrFoot = new StringBuilder(" ) MRGROUP ");

		mrStdStrFoot
				.append(" LEFT JOIN DRGS.T_ANNUALSTD STD ON STD.AS_DRG_CODE = MRGROUP.PARDRG_CODE AND STD.AS_ID = '"
						+ stdId + "' \n ");
		mrStdStrFoot.append(
				" LEFT JOIN DRGS.T_DRGS DRG ON MRGROUP.PARDRG_CODE = DRG.DRG_CODE AND MRGROUP.VER_CODE = DRG.VER_CODE WHERE 1=1 \n ");
		// 病组风险等级
		if (StringUtils.hasLength(risk)) {
			if ("5".equals(risk)) {
				mrStdStrFoot.append(" AND STD.AS_RISK_LEVEL is null \n ");
			} else {
				mrStdStrFoot.append(" AND STD.AS_RISK_LEVEL = '" + risk + "'  \n ");
			}
		}
		// DRG病组编码
		if (StringUtils.hasLength(drgCode)) {
			mrStdStrFoot.append(" AND DRG.DRG_CODE = '" + drgCode + "'  \n ");
		}
		
		// 特异病例
		StringBuilder specificHead = new StringBuilder();
		StringBuilder specificFoot = new StringBuilder();
		if (quotaCode.startsWith("SpecificCnt")) {
			specificHead.append(" SELECT SPECIFIC.* FROM ( \n ");
			specificFoot.append(" ) SPECIFIC  \n ");
			specificFoot.append(" LEFT JOIN (  \n ");
			specificFoot.append(" SELECT B.PARDRG_CODE EDRG_CODE,  \n ");
			specificFoot.append(" AVG(IP.P00800000) EAVGFEE,  \n ");
			specificFoot.append(" AVG(IP.P00320000) EAVGDAYS  \n ");
			specificFoot.append(" FROM DRGS.MR_BASEINFO A  \n ");
			specificFoot.append(" LEFT JOIN DRGS.MR_IPINFO IP  \n ");
			specificFoot.append(" ON A.PMAININDEX = IP.PMAININDEX  \n ");
			specificFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B  \n ");
			specificFoot.append(" ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
			specificFoot.append(" LEFT JOIN DRGS.T_DEPARTMENT DEP ON IP.P00300001=DEP.DEP_CODE \n ");
			specificFoot.append(" LEFT JOIN DRGS.T_ANNUALSTD C  \n ");
			specificFoot.append(" ON B.PARDRG_CODE=C.AS_DRG_CODE AND C.AS_ID='" + stdId + "'  \n ");
			// 医保病例
			if (StringUtils.hasLength(pType) && "1".equals(pType)) {
				specificFoot.append(" INNER JOIN DRGS.T_MEDINS MD ON MRBASE.PMAININDEX = MD.PARID  \n ");
			}
			specificFoot.append(
					" WHERE  instr('" + hospCode + "' , A.p00010001)>0 AND TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo + "'  \n ");
			specificFoot.append(" AND B.PARDRG_CODE IS NOT NULL  \n ");
			// DRG病组编码
			if (StringUtils.hasLength(drgCode)) {
				specificFoot.append(" AND B.PARDRG_CODE = '" + drgCode + "'  \n ");
			}
			// 科室指标象限分析:科室的上一级是全院  医师的上一级是科室
			if(StringUtils.hasLength(groupName) || StringUtils.hasLength(protomedicus) || StringUtils.hasLength(physician) || StringUtils.hasLength(resident)){
				if(StringUtils.hasLength(depCode)){
					specificFoot.append(" AND DEP.DEP_CODE = '" + depCode + "'  \n ");
				}
			}
			specificFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
			specificFoot.append(" ) E ON E.EDRG_CODE=SPECIFIC.PARDRG_CODE  WHERE  1=1   \n ");
		}

		//////////////// 特异病例数 条件//////////////////
		if ("SpecificCnt_F".equals(quotaCode)) {// 特异病例数 - 费用
			specificFoot.append(" AND SPECIFIC.COSTAMOUNT >1.2*E.EAVGFEE  \n ");
		} else if ("SpecificCnt_T".equals(quotaCode)) {// 特异病例数 - 时间
			specificFoot.append(" AND SPECIFIC.IN_DAYS>1.2*E.EAVGDAYS  \n ");
		} else if ("SpecificCnt_TF".equals(quotaCode)) {// 特异病例数 - 时间或费用
			specificFoot.append(" AND (SPECIFIC.IN_DAYS>1.2*E.EAVGDAYS OR SPECIFIC.COSTAMOUNT >1.2*E.EAVGFEE) \n ");
		}
		
		
		// 超标病例
		StringBuilder excessHead = new StringBuilder();
		StringBuilder excessFoot = new StringBuilder();
		if (quotaCode.startsWith("ExcessCnt")) {
			excessHead.append(" SELECT EXCESS.* FROM (  \n ");
			excessFoot.append(" )EXCESS    \n ");
		}
		if ("ExcessCnt_F".equals(quotaCode)) {
			// 费用
			if ("manage".equals(compareType)) {// 超绩效值
				excessFoot.append(" LEFT JOIN(SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGFEE  \n ");
				excessFoot.append(" FROM DRGS.T_QUOTAMANAGE  \n ");
				excessFoot.append(" WHERE PARDEP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARDEPGROUP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARQUOTA_CODE='AvgFee'  \n ");
				excessFoot.append(" AND PAR_MS_CODE='" + outManageCode + "'  \n ");
				excessFoot.append(" AND MANAGE_YEAR ='" + manageYear + "'  \n ");
				excessFoot.append(" ) EXMANAGECOST ON EXMANAGECOST.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
			} else if ("std".equals(compareType)) {// 超标杆值
				excessFoot.append(" LEFT JOIN(SELECT STD.AS_DRG_CODE PARDRG_CODE,STD.AS_AVG_FEE MAVGFEE,STD.AS_AVG_DAYS MAVGDAYS FROM DRGS.T_ANNUALSTD STD \n ");
				excessFoot.append(" WHERE STD.AS_ID = '"+ stdId + "' \n ");
				excessFoot.append(" ) EXMANAGECOST ON EXMANAGECOST.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
			} else {// 院内均值
				excessFoot.append(" LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00800000) MAVGFEE  \n ");
				excessFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n ");
				excessFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B     ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
				if ("1".equals(pType)) {
					excessFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
				}
				excessFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
				excessFoot.append(" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo
						+ "'  \n ");
				excessFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
				excessFoot.append(" ) EXMANAGECOST ON EXMANAGECOST.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
			}
			if (StringUtils.hasLength(typeOne) && StringUtils.hasLength(typeTwo)) {
				if ("1".equals(typeTwo)) {
					excessFoot.append(" WHERE EXCESS.COSTAMOUNT > EXMANAGECOST.MAVGFEE*(100+" + typeOne + ")/100  \n ");
				} else if ("2".equals(typeTwo)) {
					excessFoot.append(
							" WHERE EXCESS.COSTAMOUNT > EXMANAGECOST.MAVGFEE AND  EXCESS.COSTAMOUNT < EXMANAGECOST.MAVGFEE*(100+"
									+ typeOne + ")/100 \n ");
				} else if ("3".equals(typeTwo)) {
					excessFoot.append(" WHERE EXCESS.COSTAMOUNT > (100-" + typeOne
							+ ")/100*EXMANAGECOST.MAVGFEE AND  EXCESS.COSTAMOUNT <= EXMANAGECOST.MAVGFEE \n ");
				} else if ("4".equals(typeTwo)) {
					excessFoot.append(" WHERE EXCESS.COSTAMOUNT <= (100-" + typeOne + ")/100*EXMANAGECOST.MAVGFEE \n ");
				}
			} else {
				excessFoot.append(" WHERE EXCESS.COSTAMOUNT > EXMANAGECOST.MAVGFEE  \n ");
			}
		} else if ("ExcessCnt_T".equals(quotaCode)) {
			// 住院日
			if ("manage".equals(compareType)) {
				// 超绩效值
				excessFoot.append(" LEFT JOIN(SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGDAYS  \n ");
				excessFoot.append(" FROM DRGS.T_QUOTAMANAGE  \n ");
				excessFoot.append(" WHERE PARDEP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARDEPGROUP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARQUOTA_CODE='AvgDays'  \n ");
				excessFoot.append(" AND PAR_MS_CODE='" + outManageCode + "'  \n ");
				excessFoot.append(" AND MANAGE_YEAR ='" + manageYear + "'  \n ");
				excessFoot.append(" ) EXMANAGEDAY ON EXMANAGEDAY.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGEDAY.MAVGDAYS  \n ");
			} else if ("std".equals(compareType)) {// 超标杆值
				excessFoot.append(" LEFT JOIN(SELECT STD.AS_DRG_CODE PARDRG_CODE,STD.AS_AVG_FEE MAVGFEE,STD.AS_AVG_DAYS MAVGDAYS FROM DRGS.T_ANNUALSTD STD \n ");
				excessFoot.append(" WHERE STD.AS_ID = '"+ stdId + "' \n ");
				excessFoot.append(" ) EXMANAGE ON EXMANAGE.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGE.MAVGDAYS  \n ");
			} else {// 院内均值
				excessFoot.append(" LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00320000) MAVGDAYS  \n ");
				excessFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n ");
				excessFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B     ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
				if ("1".equals(pType)) {
					excessFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
				}
				excessFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
				excessFoot.append(" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo
						+ "'  \n ");
				excessFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
				excessFoot.append(" ) EXMANAGEDAY ON EXMANAGEDAY.PARDRG_CODE = EXCESS.PARDRG_CODE \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGEDAY.MAVGDAYS  \n ");
			}
		} else if ("ExcessCnt_TF".equals(quotaCode)) {
			// 费用 或 住院日
			if ("manage".equals(compareType)) {// 超绩效值
				excessFoot.append(" LEFT JOIN(SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGFEE  \n ");
				excessFoot.append(" FROM DRGS.T_QUOTAMANAGE  \n ");
				excessFoot.append(" WHERE PARDEP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARDEPGROUP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARQUOTA_CODE='AvgFee'  \n ");
				excessFoot.append(" AND PAR_MS_CODE='" + outManageCode + "'  \n ");
				excessFoot.append(" AND MANAGE_YEAR ='" + manageYear + "'  \n ");
				excessFoot.append(" ) EXMANAGECOST ON EXMANAGECOST.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" LEFT JOIN (SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGDAYS  \n ");
				excessFoot.append(" FROM DRGS.T_QUOTAMANAGE  \n ");
				excessFoot.append(" WHERE PARDEP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARDEPGROUP_CODE IS NULL  \n ");
				excessFoot.append(" AND PARQUOTA_CODE='AvgDays'  \n ");
				excessFoot.append(" AND PAR_MS_CODE='" + outManageCode + "'  \n ");
				excessFoot.append(" AND MANAGE_YEAR ='" + manageYear + "'  \n ");
				excessFoot.append(" ) EXMANAGEDAY ON EXMANAGEDAY.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGEDAY.MAVGDAYS  \n ");
				excessFoot.append(" OR EXCESS.COSTAMOUNT > EXMANAGECOST.MAVGFEE  \n ");
			} else if ("std".equals(compareType)) {// 超标杆值
				excessFoot.append(" LEFT JOIN(SELECT STD.AS_DRG_CODE PARDRG_CODE,STD.AS_AVG_FEE MAVGFEE,STD.AS_AVG_DAYS MAVGDAYS FROM DRGS.T_ANNUALSTD STD \n ");
				excessFoot.append(" WHERE STD.AS_ID = '"+ stdId + "' \n ");
				excessFoot.append(" ) EXMANAGE ON EXMANAGE.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGE.MAVGDAYS  \n ");
				excessFoot.append(" OR EXCESS.COSTAMOUNT > EXMANAGE.MAVGFEE  \n ");
			} else {// 院内均值
				excessFoot.append(" LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00800000) MAVGFEE  \n ");
				excessFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n ");
				excessFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B     ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
				if ("1".equals(pType)) {
					excessFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
				}
				excessFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
				excessFoot.append(" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo
						+ "'  \n ");
				excessFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
				excessFoot.append(" ) EXMANAGECOST ON EXMANAGECOST.PARDRG_CODE = EXCESS.PARDRG_CODE  \n ");
				excessFoot.append(" LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00320000) MAVGDAYS  \n ");
				excessFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n ");
				excessFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B  ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
				if ("1".equals(pType)) {
					excessFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
				}
				excessFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
				excessFoot.append(" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo
						+ "'  \n ");
				excessFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
				excessFoot.append(" ) EXMANAGEDAY ON EXMANAGEDAY.PARDRG_CODE = EXCESS.PARDRG_CODE \n ");
				excessFoot.append(" WHERE EXCESS.IN_DAYS > EXMANAGEDAY.MAVGDAYS  \n ");
				excessFoot.append(" OR EXCESS.COSTAMOUNT > EXMANAGECOST.MAVGFEE  \n ");
			}
		}

		/**
		 * 获取ADRG，MDC
		 */
		StringBuilder mrMdcAdrgStrHead = new StringBuilder("  SELECT MRSTD.* FROM (");
		// 预留ADRG描述、MDC代码、MDC描述
		// mrMdcAdrgStrHead.append(" ,ADRG.ADRG_DESC,MDC.MDC_CODE,MDC.MDC_DESC \n ");
		StringBuilder mrMdcAdrgStrFoot = new StringBuilder(" ) MRSTD,DRGS.T_ADRG ADRG,DRGS.T_MDCGROUP MDC WHERE  1=1  "
				+ " AND ADRG.ADRG_CODE = MRSTD.ADRG_CODE  AND ADRG.VER_CODE = MRSTD.VER_CODE "
				+ " AND MDC.MDC_CODE = ADRG.MDC_CODE AND MDC.VER_CODE = ADRG.VER_CODE ");
		// MDC病组编码
		if (StringUtils.hasLength(mdcCode)) {
			mrMdcAdrgStrFoot.append(" AND MDC.MDC_CODE = '" + mdcCode + "'  \n ");
		}

		// ADRG病组编码
		if (StringUtils.hasLength(adrgCode)) {
			mrMdcAdrgStrFoot.append(" AND ADRG.ADRG_CODE = '" + adrgCode + "'  \n ");
		}
		/**
		 * 获取诊断、手术、药品费
		 */
		StringBuilder mrDiaOperCostStrHead = new StringBuilder(
				" SELECT  MRMDCADRG.*,DIAG.PD0101 DIAGNOSE_CODE_MAIN,'('||DIAG.PD0101||')'||DIAG.PD0100 DIAGNOSE_DESC_MAIN,COSTSUM.DRUG_COST,DIAGINFO.DIAGINFO OTHERDIAG,OPERINFO.OPERINFO OPERS  FROM ( ");

		StringBuilder mrDiaOperCostStrFoot = new StringBuilder("");
		mrDiaOperCostStrFoot.append(" ) MRMDCADRG  \n ");
		mrDiaOperCostStrFoot.append(
				" LEFT JOIN DRGS.MR_DIAGNOSE DIAG ON DIAG.PMAININDEX = MRMDCADRG.PMAININDEX AND DIAG.PDTYPE = 'M'  \n ");
		mrDiaOperCostStrFoot.append(
				" LEFT JOIN DRGS.MR_OPERATION OPER ON OPER.PMAININDEX = MRMDCADRG.PMAININDEX AND OPER.POTYPE = 'M' AND OPER.PO1400=1  \n ");
		mrDiaOperCostStrFoot.append(
				" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG('('||DIAG.PD0101||')'||DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  DIAGINFO \n");
		mrDiaOperCostStrFoot.append(
				" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=IP.PMAININDEX \n");
		mrDiaOperCostStrFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0  AND TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '"
				+ dateTo + "' AND DIAG.PDTYPE = 'O' \n");
		mrDiaOperCostStrFoot
				.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON MRMDCADRG.PMAININDEX = DIAGINFO.PMAININDEX  \n");
		mrDiaOperCostStrFoot.append(
				" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG('('||OPER.PO0101||')'||OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO \n");
		mrDiaOperCostStrFoot.append(
				" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=IP.PMAININDEX \n");
		mrDiaOperCostStrFoot.append(
				" WHERE instr('" + hospCode + "' , BA.p00010001)>0  AND TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo + "' \n");
		mrDiaOperCostStrFoot
				.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON MRMDCADRG.PMAININDEX = OPERINFO.PMAININDEX \n");
		mrDiaOperCostStrFoot
				.append(" LEFT JOIN DRGS.MR_EXPENSE_SUM COSTSUM ON MRMDCADRG.PMAININDEX = COSTSUM.PMAININDEX  \n ");

		/**
		 * 次均费用(元)(绩效值),平均住院日(绩效值)
		 */
		StringBuilder mrManageStrHead = new StringBuilder(
				"SELECT MRDIAOPERCOST.*,MANAGEAVGFEE.MAVGFEE COMPAREGFEE,MANAGEAVGDAYS.MAVGDAYS COMPAREGDAYS FROM ( ");

		StringBuilder mrManageStrFoot = new StringBuilder(" ) MRDIAOPERCOST "
				+ " LEFT JOIN (SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGFEE FROM DRGS.T_QUOTAMANAGE WHERE  1=1  "
				+ " AND PARDEP_CODE IS NULL " + " AND PARDEPGROUP_CODE IS NULL " + " AND MANAGE_YEAR = '" + manageYear
				+ "' " + " AND PAR_MS_CODE = '" + outManageCode + "' " + " AND PARQUOTA_CODE = 'AvgFee' "
				+ " AND PATIENT_TYPE = '" + pType
				+ "') MANAGEAVGFEE ON MANAGEAVGFEE.PARDRG_CODE = MRDIAOPERCOST.PARDRG_CODE AND ROWNUM = 1 "
				+ " LEFT JOIN (SELECT PARDRG_CODE, MANAGE_BENCHMARK MAVGDAYS FROM DRGS.T_QUOTAMANAGE WHERE  1=1  "
				+ " AND PARDEP_CODE IS NULL " + " AND PARDEPGROUP_CODE IS NULL " + " AND MANAGE_YEAR = '" + manageYear
				+ "' " + " AND PAR_MS_CODE = '" + outManageCode + "' " + " AND PARQUOTA_CODE = 'AvgDays' "
				+ " AND PATIENT_TYPE = '" + pType
				+ "') MANAGEAVGDAYS ON MANAGEAVGDAYS.PARDRG_CODE = MRDIAOPERCOST.PARDRG_CODE AND ROWNUM = 1");

		/**
		 * 次均费用(元)(标杆值),平均住院日(标杆值)
		 */

		StringBuilder mrBenchmarkStrHead = new StringBuilder(
				"SELECT MRMANAGERESULT.*,BENCHMARKAVGFEE.BKAVGFEE COMPAREGFEE,BENCHMARKAVGDAYS.BKAVGDAYS COMPAREGDAYS FROM ( ");

		StringBuilder mrBenchmarkStrFoot = new StringBuilder("  ) MRMANAGERESULT "
				/**
				 * 次均费用
				 */
				+ " LEFT JOIN (SELECT MST.MANAGE_BENCHMARK BKAVGFEE,MST.PARDRG_CODE  FROM DRGS.T_MANAGESUMMARYDTL MST WHERE  1=1  "
				+ " AND MST.PAR_MS_CODE = '" + outManageCode + "' " + " AND STD_YEAR = '" + manageYear + "' "
				+ " AND MST.PARQUOTA_CODE = 'AvgFee' " + " AND MST.PATIENT_TYPE = '" + pType + "'"
				+ " ) BENCHMARKAVGFEE on BENCHMARKAVGFEE.PARDRG_CODE = MRMANAGERESULT.PARDRG_CODE "

				/**
				 * 平均住院日
				 */
				+ " LEFT JOIN (SELECT MST.MANAGE_BENCHMARK BKAVGDAYS,MST.PARDRG_CODE  FROM DRGS.T_MANAGESUMMARYDTL MST WHERE  1=1  "
				+ " AND MST.PAR_MS_CODE = '" + outManageCode + "' " + " AND STD_YEAR = '" + manageYear + "' "
				+ " AND MST.PARQUOTA_CODE = 'AvgDays' " + " AND MST.PATIENT_TYPE = '" + pType + "'"
				+ " ) BENCHMARKAVGDAYS on BENCHMARKAVGDAYS.PARDRG_CODE = MRMANAGERESULT.PARDRG_CODE ");
		StringBuilder hospAvgHead = new StringBuilder();

		StringBuilder hospAvgFoot = new StringBuilder();
		hospAvgHead.append(
				" SELECT MRDIAOPERCOST.*,ROUND(HOSPAVGCOST.HOSPAVGFEE,2) COMPAREGFEE,ROUND(HOSPAVGDAY.HOSPAVGDAYS,2) COMPAREGDAYS FROM ( \n");

		hospAvgFoot.append(" ) MRDIAOPERCOST LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00800000) HOSPAVGFEE  \n ");
		hospAvgFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n ");
		hospAvgFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B     ON A.PMAININDEX=B.PARID   and b.ver_region='"+verRegion+"' \n ");
		if ("1".equals(pType)) {
			hospAvgFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
		}
		hospAvgFoot.append(" WHERE instr('" + hospCode + "' , BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
		hospAvgFoot.append(
				" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo + "'  \n ");
		hospAvgFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
		hospAvgFoot.append(" ) HOSPAVGCOST ON HOSPAVGCOST.PARDRG_CODE = MRDIAOPERCOST.PARDRG_CODE  \n ");
		hospAvgFoot.append(" LEFT JOIN(SELECT B.PARDRG_CODE, AVG(A.P00320000) HOSPAVGDAYS  \n ");
		hospAvgFoot.append(" FROM  DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n ");
		hospAvgFoot.append(" LEFT JOIN DRGS.T_PATIENTDRG B     ON A.PMAININDEX=B.PARID  and b.ver_region='"+verRegion+"' \n ");
		if ("1".equals(pType)) {
			hospAvgFoot.append(" LEFT JOIN DRGS.T_MEDINS D ON D.PARID=A.PMAININDEX  \n ");
		}
		hospAvgFoot.append(" WHERE instr('" + hospCode + "', BA.p00010001)>0 AND B.PARDRG_CODE IS NOT NULL  \n ");
		hospAvgFoot.append(
				" AND TO_CHAR(A.P00290000,'YYYY-MM-DD') BETWEEN  '" + dateFrom + "'  AND '" + dateTo + "'  \n ");
		hospAvgFoot.append(" GROUP BY B.PARDRG_CODE  \n ");
		hospAvgFoot.append(" ) HOSPAVGDAY ON HOSPAVGDAY.PARDRG_CODE = MRDIAOPERCOST.PARDRG_CODE \n ");
		/**
		 * 搜索条件
		 */
		StringBuilder mrSearchStrHead = new StringBuilder(" SELECT CASERESULT.* FROM ( ");
		StringBuilder mrSearchStrFoot = new StringBuilder(
				" ) CASERESULT WHERE " + "INSTR( CASERESULT.MR_NO,'" + type2 + "') > 0 OR "// 病案号搜索
						+ " INSTR( CASERESULT.DEP_NAME,'" + type2 + "') > 0 OR " 
						+ " INSTR( CASERESULT.PATIENT_NAME,'" + type2 + "') > 0 " );//姓名搜索
		if (!("1".equals(isGt60))) {
			mrSearchStrFoot.append(" OR  INSTR( CASERESULT.PARDRG_CODE,'" + type2 + "') > 0 ");// DRG病组搜索
		}

		/**
		 * 开始拼接执行sql
		 */

		String getCaseListSql = "";// 获取病例数据sql
		String getCaseCountSql = "";// 获取病例总数sql

		if (StringUtils.hasLength(type2)) {
			getCaseListSql += mrSearchStrHead;
			getCaseCountSql += mrSearchStrHead;
		}
		// 判断条件1：是否大于60天病例 或 是否未入组病例
		Boolean condition1 = ("Over60CaseCnt".equals(quotaCode)) || ("NotInCaseCnt".equals(quotaCode));
		// 绩效比:manager_inner
		if (!condition1 && "manager_inner".equals(outManageCode)) {
			// 获取次均费用(元)(绩效值),平均住院日(绩效值)
			getCaseListSql += mrManageStrHead.toString();
		}
		// 标杆比:std_inner
		if (!condition1 && "std_computer".equals(outManageCode)) {
			// 获取次均费用(元)(标杆值),平均住院日(标杆值)
			getCaseListSql += mrBenchmarkStrHead.toString();
		}
		// 院内均值
		if ("hosp".equals(compareType)) {
			getCaseListSql += hospAvgHead.toString();
		}
		// 获取主要诊断、主要手术、药品费
		getCaseListSql += mrDiaOperCostStrHead.toString();

		if (StringUtils.hasLength(mdcCode) || StringUtils.hasLength(adrgCode)) {
			// 获取ADRG，MDC
			getCaseListSql += mrMdcAdrgStrHead.toString();
			getCaseCountSql += mrMdcAdrgStrHead.toString();
		}
		// 特异病例
		if (quotaCode.startsWith("SpecificCnt")) {
			getCaseListSql += specificHead.toString();
			getCaseCountSql += specificHead.toString();
		}
		// 超标病例
		if (quotaCode.startsWith("ExcessCnt")) {
			getCaseListSql += excessHead.toString();
			getCaseCountSql += excessHead.toString();
		}

		if (!condition1) {
			// 获取标杆信息
			getCaseListSql += mrStdStrHead.toString();
			getCaseCountSql += mrStdStrHead.toString();
		}

		if (!("1".equals(isGt60))) {
			// 获取分组信息
			getCaseListSql += mrGroupStrHead.toString();
			getCaseCountSql += mrGroupStrHead.toString();
		}

		// 获取基本信息和住院信息
		getCaseListSql += mrBaseStr.toString();
		getCaseCountSql += mrBaseStr.toString();

		if (!("1".equals(isGt60))) {
			// 分组查询条件
			getCaseListSql += mrGroupStrFoot.toString();
			getCaseCountSql += mrGroupStrFoot.toString();
		}
		if (!condition1) {
			// 标杆查询条件
			getCaseListSql += mrStdStrFoot.toString();
			getCaseCountSql += mrStdStrFoot.toString();
		}

		// 超标病例
		if (quotaCode.startsWith("ExcessCnt")) {
			getCaseListSql += excessFoot.toString();
			getCaseCountSql += excessFoot.toString();
		}
		// 特异病例
		if (quotaCode.startsWith("SpecificCnt")) {
			getCaseListSql += specificFoot.toString();
			getCaseCountSql += specificFoot.toString();
		}

		if (StringUtils.hasLength(mdcCode) || StringUtils.hasLength(adrgCode)) {
			// MDC、ADRG信息查询条件
			getCaseListSql += mrMdcAdrgStrFoot.toString();
			getCaseCountSql += mrMdcAdrgStrFoot.toString();
		}
		// 主要诊断、主要手术、药品费查询条件
		getCaseListSql += mrDiaOperCostStrFoot.toString();

		// 绩效比:manager_inner
		if (!condition1 && "manager_inner".equals(outManageCode)) {
			// 次均费用(元)(绩效值),平均住院日(绩效值)查询条件
			getCaseListSql += mrManageStrFoot.toString();
		}

		// 标杆比:std_inner
		if (!condition1 && "std_computer".equals(outManageCode)) {
			// 获取次均费用(元)(标杆值),平均住院日(标杆值)
			getCaseListSql += mrBenchmarkStrFoot.toString();
		}

		// 院内均值
		if ("hosp".equals(compareType)) {
			getCaseListSql += hospAvgFoot.toString();
		}

		if (StringUtils.hasLength(type2)) {
			getCaseListSql += mrSearchStrFoot;
			getCaseCountSql += mrSearchStrFoot;
		}
		if ("caseCount".equals(sqlType)) {// 获取病例总数
			return getCaseCountSql;
		} else {// 获取病例明细
			return getCaseListSql;
		}
	}

	/**
	 * @Description：获取已导入病例明细
	 * @Author mazhaoyan
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#findImportedCaseList(int,
	 *      int, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findImportedCaseList(int page, int pageSize, String date, String type2,
			String orderColumn, String orderMethod, String stdId,String groupScheme) {
		// 不分页,每页默认99999999
		if (pageSize == -1) {
			pageSize = 99999999;
		}
		// 获取执行sql
		String caseListSql = getImportedCaseSql(date, "caseList", type2,stdId,groupScheme);

		String pageSqlHead = "SELECT * FROM (SELECT PAGERESULT.*, ROWNUM RN FROM (";

		String pageSqlFoot = " ORDER BY " + orderColumn + " " + orderMethod + ") PAGERESULT ) WHERE RN > " + page
				+ " AND RN <= " + (page + pageSize);

		List<Map<String, Object>> mrListMaps = jdbcTemplateObj.queryForList(pageSqlHead + caseListSql + pageSqlFoot);

		return mrListMaps;
	}

	@Override
	public List<Map<String, Object>> findImportedHcCaseList(int page, int pageSize, String date, String type2,
			String orderColumn, String orderMethod, String stdId,String groupScheme) {
		// 不分页,每页默认99999999
		if (pageSize == -1) {
			pageSize = 99999999;
		}
		// 获取执行sql
		String caseListSql = getImportedHcCaseSql(date, "caseList", type2, stdId,groupScheme);

		String pageSqlHead = "SELECT * FROM (SELECT PAGERESULT.*, ROWNUM RN FROM (";
		String pageSqlFoot = " ORDER BY " + orderColumn + " " + orderMethod + ") PAGERESULT ) WHERE RN > " + page
				+ " AND RN <= " + (page + pageSize);

		List<Map<String, Object>> mrListMaps = jdbcTemplateObj.queryForList(pageSqlHead + caseListSql + pageSqlFoot);

		return mrListMaps;
	}

	private String getImportedHcCaseSql(String date, String type, String type2,String stdId,String groupScheme) {
		StringBuilder sqlbase = new StringBuilder();
		// 基本信息
		sqlbase.append(
				"SELECT BASE.PMAININDEX,BASE.P00050000 MR_NO,BASE.P00060000 PATIENT_NAME,BASE.P00020001 PAYMENT,BASE.P00070001 SEX,BASE.P00090000 AGE_YEAR,BASE.P00090001 AGE_MONTH, \n");
		sqlbase.append(
				"BASE.P00090002 AGE_DAYS,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') IN_DATE,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') OUT_DATE,IP.P00300001 DEP_CODE,IP.P00620001 OUT_WAY, \n");
		sqlbase.append(
				"IP.P00300000 DEP_NAME,IP.P00320000 IN_DAYS,IP.P00800000 COSTAMOUNT,IP.P00600300 GROUPDOCTOR, \n");
		sqlbase.append("DIC.DIC_DESC,M.FUND_PAY \n");
		sqlbase.append("FROM DRGS.MR_BASEINFO BASE LEFT JOIN DRGS.MR_IPINFO IP ON BASE.PMAININDEX = IP.PMAININDEX \n");
		sqlbase.append("RIGHT JOIN DRGS.T_MEDINS M ON BASE.PMAININDEX=M.PARID \n");
		sqlbase.append(
				"LEFT JOIN ( SELECT DIC_VALUE,DIC_DESC FROM DRGS.MR_DICTIONARYDATA WHERE DIC_CODE='MED_INS_TYPE')DIC ON M.HI_TYPE=DIC.DIC_VALUE \n");
		sqlbase.append("WHERE 1 = 1 AND TO_CHAR(IP.P00290000,'YYYY-MM') = '" + date + "' \n");
		StringBuilder drgHead = new StringBuilder();
		StringBuilder drgFoot = new StringBuilder();
		drgHead.append("SELECT MRBASE.*,PD.PARDRG_CODE,PD.VER_CODE FROM ( \n");
		drgFoot.append(")MRBASE LEFT JOIN DRGS.T_PATIENTDRG PD  ON MRBASE.PMAININDEX = PD.PARID and PD.ver_region='"+groupScheme+"' \n");
		/**
		 * 搜索条件
		 */
		StringBuilder mrSearchStrHead = new StringBuilder(" SELECT CASERESULT.* FROM ( ");

		StringBuilder mrSearchStrFoot = new StringBuilder(
				" ) CASERESULT WHERE " + "INSTR( CASERESULT.MR_NO,'" + type2 + "') > 0 OR "// 病案号搜索
						+ "INSTR( CASERESULT.DEP_NAME,'" + type2 + "') > 0 OR "// 科室名称搜索
						+ "INSTR( CASERESULT.PARDRG_CODE,'" + type2 + "') > 0 ");// DRG病组搜索

		StringBuilder sqlOtherHead = new StringBuilder();
		StringBuilder sqlOtherFoot = new StringBuilder();
		sqlOtherHead.append("SELECT PDRG.*,DIAG.PD0101 MAIN_DIAG_CODE,DIAG.PD0100 MAIN_DIAG_DESC,\n");
		sqlOtherHead
				.append("COSTSUM.DRG_MED_COST,COSTSUM.DRG_NURS_COST,COSTSUM.DRG_TECH_COST,COSTSUM.DRG_DRUG_COST,\n");
		sqlOtherHead.append(
				"COSTSUM.DRG_MANAGE_COST,DRG.DRG_CODE,DRG.DRG_DESC,MDC.MDC_CODE,MDC.MDC_DESC,DIAGINFO.DIAGINFO,OPERINFO.OPERINFO,STD.AS_WEIGHT,OPER.PO0400 OPERDOCTOR FROM( \n");
		// 主要诊断
		sqlOtherFoot.append(
				") PDRG LEFT JOIN DRGS.MR_DIAGNOSE DIAG ON DIAG.PMAININDEX = PDRG.PMAININDEX AND DIAG.PDTYPE = 'M' \n");
		// 主要手术
		sqlOtherFoot.append(
				" LEFT JOIN DRGS.MR_OPERATION OPER ON OPER.PMAININDEX = PDRG.PMAININDEX AND OPER.POTYPE = 'M' \n");

		// 费用五大类
		sqlOtherFoot.append("LEFT JOIN DRGS.MR_EXPENSE_SUM COSTSUM ON PDRG.PMAININDEX = COSTSUM.PMAININDEX \n");
		// 病组信息
		sqlOtherFoot.append("LEFT JOIN DRGS.T_ANNUALSTD STD  ON PDRG.PARDRG_CODE = STD.AS_DRG_CODE AND STD.AS_ID ='"
				+ stdId + "' \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_DRGS DRG  ON DRG.DRG_CODE = PDRG.PARDRG_CODE AND DRG.VER_CODE = PDRG.VER_CODE \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_ADRG ADRG  ON ADRG.ADRG_CODE = DRG.ADRG_CODE AND DRG.VER_CODE = ADRG.VER_CODE \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_MDCGROUP MDC  ON ADRG.MDC_CODE = MDC.MDC_CODE AND ADRG.VER_CODE = MDC.VER_CODE \n");
		// 诊断
		sqlOtherFoot.append(
				" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  DIAGINFO  \n ");
		sqlOtherFoot.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP \n ");
		sqlOtherFoot.append(" ON DIAG.PMAININDEX = IP.PMAININDEX \n ");
		sqlOtherFoot.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM')  = '" + date + "' AND DIAG.PDTYPE = 'O'   \n ");
		sqlOtherFoot.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON PDRG.PMAININDEX = DIAGINFO.PMAININDEX \n ");
		// 手术
		sqlOtherFoot.append(
				" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO  \n ");
		sqlOtherFoot.append(" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP \n ");
		sqlOtherFoot.append(" ON OPER.PMAININDEX = IP.PMAININDEX \n ");
		sqlOtherFoot.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM')  = '" + date + "'  \n ");
		sqlOtherFoot.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON PDRG.PMAININDEX = OPERINFO.PMAININDEX  \n ");

		String getCaseCountSql = drgHead.toString() + sqlbase.toString() + drgFoot.toString();
		if (StringUtils.hasLength(type2)) {
			getCaseCountSql = mrSearchStrHead.toString() + getCaseCountSql + mrSearchStrFoot.toString();
		}
		String getCaseListSql = sqlOtherHead.toString() + getCaseCountSql + sqlOtherFoot.toString();
		if ("caseCount".equals(type)) {// 获取病例总数
			return getCaseCountSql;
		} else {// 获取病例明细
			return getCaseListSql;
		}

	}

	/**
	 * @Description：
	 * 
	 * @Author mazhaoyan
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#findImportedCaseCount(int,
	 *      int, java.lang.String)
	 */
	@Override
	public int findImportedCaseCount(int page, int pageSize, String date, String type2,String stdId,String groupScheme) {
		String caseCountSql = getImportedCaseSql(date, "caseCount", type2,stdId,groupScheme);
		String caseCountHead = "SELECT COUNT(*) caseCount FROM ( ";
		String caseCountFoot = " ) ";
		return jdbcTemplateObj.queryForObject(caseCountHead + caseCountSql + caseCountFoot, Integer.class);
	}

	@Override
	public int findImportedHcCaseCount(int page, int pageSize, String date, String type2,String stdId,String groupScheme) {
		String caseCountSql = getImportedHcCaseSql(date, "caseCount", type2, stdId,groupScheme);
		String caseCountHead = "SELECT COUNT(*) caseCount FROM ( ";
		String caseCountFoot = " ) ";
		return jdbcTemplateObj.queryForObject(caseCountHead + caseCountSql + caseCountFoot, Integer.class);
	}

	private String getImportedCaseSql(String date, String type, String type2 ,String stdId,String groupScheme) {
		StringBuilder sqlbase = new StringBuilder();
		// 基本信息
		sqlbase.append(
				"SELECT BASE.PMAININDEX ,BASE.P00040000 IP_COUNT,BASE.P00050000 MR_NO,BASE.P00060000 PATIENT_NAME,DD.DIC_DESC PAYMENT,BASE.P00070001 SEX,BASE.P00090000 AGE_YEAR,BASE.P00090001 AGE_MONTH, \n");
		sqlbase.append(
				"BASE.P00090002 AGE_DAYS,TO_CHAR(IP.P00250000, 'YYYY-MM-DD') IN_DATE,TO_CHAR(IP.P00290000, 'YYYY-MM-DD') OUT_DATE,IP.P00300001 DEP_CODE,IP.P00620001 OUT_WAY, \n");
		sqlbase.append(
				"IP.P00300000 DEP_NAME,IP.P00320000 IN_DAYS,IP.P00800000 COSTAMOUNT,IP.P00600300 GROUPDOCTOR \n");
		sqlbase.append("FROM DRGS.MR_BASEINFO BASE LEFT JOIN DRGS.MR_IPINFO IP ON BASE.PMAININDEX = IP.PMAININDEX \n");
		sqlbase.append("LEFT JOIN DRGS.MR_DICTIONARYDATA DD ON BASE.P00020001 = DD.DIC_VALUE AND DD.DIC_CODE = 'P00020001'  \n"); 
		sqlbase.append("WHERE 1 = 1 AND TO_CHAR(IP.P00290000,'YYYY-MM') = '" + date + "' \n");
		StringBuilder drgHead = new StringBuilder();
		StringBuilder drgFoot = new StringBuilder();
		drgHead.append("SELECT MRBASE.*,PD.PARDRG_CODE,PD.VER_CODE FROM ( \n");
		drgFoot.append(")MRBASE LEFT JOIN DRGS.T_PATIENTDRG PD  ON MRBASE.PMAININDEX = PD.PARID and PD.ver_region='"+groupScheme+"' \n");
		/**
		 * 搜索条件
		 */
		StringBuilder mrSearchStrHead = new StringBuilder(" SELECT CASERESULT.* FROM ( ");

		StringBuilder mrSearchStrFoot = new StringBuilder(
				" ) CASERESULT WHERE " + "INSTR( CASERESULT.MR_NO,'" + type2 + "') > 0 OR "// 病案号搜索
						+ "INSTR( CASERESULT.DEP_NAME,'" + type2 + "') > 0 OR "// 科室名称搜索
						+ "INSTR( CASERESULT.PARDRG_CODE,'" + type2 + "') > 0 ");// DRG病组搜索

		StringBuilder sqlOtherHead = new StringBuilder();
		StringBuilder sqlOtherFoot = new StringBuilder();
		sqlOtherHead.append("SELECT PDRG.*,DIAG.PD0101 MAIN_DIAG_CODE,DIAG.PD0100 MAIN_DIAG_DESC,\n");
		sqlOtherHead
				.append("COSTSUM.DRG_MED_COST,COSTSUM.DRG_NURS_COST,COSTSUM.DRG_TECH_COST,COSTSUM.DRG_DRUG_COST,\n");
		sqlOtherHead.append(
				"COSTSUM.DRG_MANAGE_COST,DRG.DRG_CODE,DRG.DRG_DESC,MDC.MDC_CODE,MDC.MDC_DESC,DIAGINFO.DIAGINFO,OPERINFO.OPERINFO,STD.AS_WEIGHT,OPER.PO0400 OPERDOCTOR FROM( \n");
		// 主要诊断
		sqlOtherFoot.append(
				") PDRG LEFT JOIN DRGS.MR_DIAGNOSE DIAG ON DIAG.PMAININDEX = PDRG.PMAININDEX AND DIAG.PDTYPE = 'M' \n");
		// 主要手术
		sqlOtherFoot.append(
				" LEFT JOIN DRGS.MR_OPERATION OPER ON OPER.PMAININDEX = PDRG.PMAININDEX AND OPER.POTYPE = 'M' \n");

		// 费用五大类
		sqlOtherFoot.append("LEFT JOIN DRGS.MR_EXPENSE_SUM COSTSUM ON PDRG.PMAININDEX = COSTSUM.PMAININDEX \n");
		// 病组信息
		sqlOtherFoot.append("LEFT JOIN DRGS.T_ANNUALSTD STD  ON PDRG.PARDRG_CODE = STD.AS_DRG_CODE AND STD.AS_ID ='"
				+ stdId + "' \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_DRGS DRG  ON DRG.DRG_CODE = PDRG.PARDRG_CODE AND DRG.VER_CODE = PDRG.VER_CODE \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_ADRG ADRG  ON ADRG.ADRG_CODE = DRG.ADRG_CODE AND DRG.VER_CODE = ADRG.VER_CODE \n");
		sqlOtherFoot.append(
				"LEFT JOIN DRGS.T_MDCGROUP MDC  ON ADRG.MDC_CODE = MDC.MDC_CODE AND ADRG.VER_CODE = MDC.VER_CODE \n");
		// 诊断
		sqlOtherFoot.append(
				" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  DIAGINFO  \n ");
		sqlOtherFoot.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP \n ");
		sqlOtherFoot.append(" ON DIAG.PMAININDEX = IP.PMAININDEX \n ");
		sqlOtherFoot.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM')  = '" + date + "' AND DIAG.PDTYPE = 'O'   \n ");
		sqlOtherFoot.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON PDRG.PMAININDEX = DIAGINFO.PMAININDEX \n ");
		// 手术
		sqlOtherFoot.append(
				" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  OPERINFO  \n ");
		sqlOtherFoot.append(" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP \n ");
		sqlOtherFoot.append(" ON OPER.PMAININDEX = IP.PMAININDEX \n ");
		sqlOtherFoot.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM')  = '" + date + "'  \n ");
		sqlOtherFoot.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON PDRG.PMAININDEX = OPERINFO.PMAININDEX  \n ");

		String getCaseCountSql = drgHead.toString() + sqlbase.toString() + drgFoot.toString();
		if (StringUtils.hasLength(type2)) {
			getCaseCountSql = mrSearchStrHead.toString() + getCaseCountSql + mrSearchStrFoot.toString();
		}
		String getCaseListSql = sqlOtherHead.toString() + getCaseCountSql + sqlOtherFoot.toString();
		if ("caseCount".equals(type)) {// 获取病例总数
			return getCaseCountSql;
		} else {// 获取病例明细
			return getCaseListSql;
		}
	}

	/**
	 * 
	 * @Description：获取指标小数点
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getQuotas(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getQuotas' + #quotaCode ")
	public List<Object[]> getQuotas(String quotaCode) {
		String sql = "select quota_code, unit,quota_desc from DRGs.t_quotaselectdtl where quota_id='" + quotaCode
				+ "' order by indexes ";
		List<Object[]> resultList = getRepository().createSqlQuery(sql).list();
		return resultList;
	}

	/**
	 * 
	 * @Description：获得出院病历的分组的最新日期
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getLatestDate()
	 */

	@Override
	public String getLatestDate(String groupScheme) {
		String roleName = CurrentUser.getRoleName();
		String hospName = GlobalData.hospName;
		StringBuilder sql = new StringBuilder();
		sql.append(" select max(ipinfo.P00290000) from drgs.mr_ipinfo ipinfo \n ");
		sql.append(" left join drgs.t_patientdrg b \n ");
		sql.append(" on ipinfo.pmainindex=b.parid and b.ver_region='"+groupScheme+"'   \n ");
		sql.append(" where b.parid is not null \n ");
		if(StringUtils.hasLength(hospName)&&"清华长庚".equals(hospName)&&StringUtils.hasLength(roleName)&&"科室".equals(roleName)){
			sql.append(" and a.confirmflag=1 \n ");
		}
		List<Object> resultList = getRepository().createSqlQuery(sql.toString()).list();
		if (resultList.size() > 0) {
			return resultList.get(0) != null ? resultList.get(0).toString().substring(0, 10) : null;
		}
		return null;
	}

	/**
	 * 
	 * @Description：获取绩效标杆和标杆比信息
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getManageSummary(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getManageSummary(String msType) {
		String sql = "select  ms.ms_code MS_CODE,dc.dic_desc MS_DESC from drgs.t_managesummary ms \n" + 
				" left join drgs.MR_DICTIONARYDATA dc on ms.ms_code=dc.dic_value \n" + 
				" where  ms.ms_code like 'std_%'  and ms_flag='1' and dc.dic_code='STD_TARGET' ";
		if (msType.equals("manager_")) {
			sql = "select  ms.ms_code MS_CODE,dc.dic_desc MS_DESC from drgs.t_managesummary ms \n" + 
				  "left join drgs.MR_DICTIONARYDATA dc on ms.ms_code=dc.dic_value \n" + 
				  "where  ms.ms_code like 'manager_%'  and ms_flag='1' and dc.dic_code='MANAGE_TARGET' ";
		}
		return jdbcTemplateObj.queryForList(sql);
	}

	/**
	 * 
	 * @Description：获取分组数据有哪些年份的数据
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getGroupAllYears(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getGroupAllYears' + #result.pardepgroupCode + #result.pardepCode + #result.pargroupName + #result.protomedicus + #result.physician + #result.resident")
	public List<String> getGroupAllYears(IndexCal result) {
		String sql = " select distinct(to_char(t.P00290000,'yyyy')) mrdate from DRGs.MR_IPINFO t ";
		sql += " left join DRGs.t_department d ";
		sql += " on t.P00300001=d.dep_code ";
		sql += " where  1=1  ";
		if (StringUtils.hasLength(result.getPardepgroupCode())) {
			sql += " and d.pardepgroup_code='" + result.getPardepgroupCode() + "'";
		}
		if (StringUtils.hasLength(result.getPardepCode())) {
			sql += " and d.dep_code='" + result.getPardepCode() + "'";
		}
		if (StringUtils.hasLength(result.getPargroupName())) {
			sql += " and t.P00601001='" + result.getPargroupName() + "'";
		}
		if (StringUtils.hasLength(result.getProtomedicus())) {
			sql += " and t.P00600201='" + result.getProtomedicus() + "'";
		}
		if (StringUtils.hasLength(result.getPhysician())) {
			sql += " and t.P00600301='" + result.getPhysician() + "'";
		}
		if (StringUtils.hasLength(result.getResident())) {
			sql += " and t.P00600401='" + result.getResident() + "'";
		}

		sql += " order by mrdate desc ";
		// List<String> years = getRepository().createSqlQuery(sql).list();
		List<String> years = jdbcTemplateObj.queryForList(sql, String.class);
		return years;
	}

	/**
	 * 
	 * @Description：获取绩效指标代码
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getManagerQuotaCode()
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getManagerQuotaCode'+ #year+#pType+#msCode")
	public List<String> getManagerQuotaCode(String year, String pType, String msCode) {
		String sql = "";
		if (msCode != null && msCode.contains("std_")) {
			sql = "select distinct t.parquota_code  from drgs.T_MANAGESUMMARYDTL t  where t.par_ms_code='" + msCode
					+ "' and t.std_year='" + year + "' and t.patient_type='" + pType + "'";

		} else if (msCode != null && msCode.contains("manager_")) {
			sql = "select distinct t.parquota_code  from drgs.t_quotamanage t  where t.par_ms_code = '" + msCode
					+ "' and t.manage_year='" + year + "' and t.patient_type='" + pType + "'";
		}
		return jdbcTemplateObj.queryForList(sql, String.class);
	}

	/**
	 * 
	 * @Description：获取历史分组器版本
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getHisGroupVersion()
	 */
	@Override
	public List<Map<String, Object>> getHisGroupVersion(IndexCalDTO dto) {
		String dateFrom=DrgsIndex.formateDateFrom(dto.getDateFrom()).substring(0,7);
		String sql = "SELECT distinct t.ver_code VER_CODE,v.ver_desc VER_DESC FROM drgs.t_patientdrg_h t \n"
				+ " LEFT JOIN drgs.t_drgversion v ON t.ver_code=v.ver_code \n "
				+"LEFT JOIN drgs.mr_ipinfo ip on t.parid=ip.pmainindex \n" ;
		if(StringUtils.hasLength(dateFrom)){
			sql+="where to_char(ip.p00290000,'yyyy-mm') ='"+dateFrom+"'";
		}
		return jdbcTemplateObj.queryForList(sql);
	}

	/**
	 * 
	 * @Description：获取分组年份
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getMrDate()
	 */
	@Override
	public Object getMrDate() {
		String sql = " select distinct(to_char(t.P00290000,'yyyy')) mrdate from DRGs.MR_IPINFO t left join drgs.t_patientdrg p on t.pmainindex=p.parid ";
		return jdbcTemplateObj.queryForList(sql, String.class);
	}

	/**
	 * @Description：获取系统目前存在的标杆年份
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getStdYear()
	 */
	@Override
	public List<Map<String, Object>> getStdYear() {
		String sql = " SELECT Distinct t.as_year STD_YEAR FROM drgs.t_annualstd t ORDER BY t.as_year DESC ";
		return jdbcTemplateObj.queryForList(sql);
	}
	
	/**
	 * 
	 *  getStdType.  
	 *  @Description：获取标杆类型
	 *  @Author fulianxia
	 *  @return 
	 *  @Date:2018年1月11日
	 */
	@Override
	public List<Map<String, Object>> getStdType() {
		String sql = " SELECT DISTINCT T.AS_STD_TYPE,MR.DIC_DESC FROM DRGS.T_ANNUALSTD T JOIN (SELECT * FROM DRGS.MR_DICTIONARYDATA T WHERE T.DIC_CODE='AS_STD_TYPE') MR ON T.AS_STD_TYPE=MR.DIC_VALUE ";
		return jdbcTemplateObj.queryForList(sql);
	}

	/**
	 * 
	 * @Description：获取指标详情
	 * @Author lhj
	 * @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getQuotaDetail(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getQuotaDetail'+ #code")
	public Map<String, Object> getQuotaDetail(String code) {
		Map<String, Object> resultMap = new HashMap<>();
		try {
			resultMap = jdbcTemplateObj.queryForMap(
					"SELECT t.quota_code,t.text,t.unit,t.select_sql FROM drgs.t_quotaselect t where t.quota_code='"
							+ code + "'");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultMap;
	}
	/**
	 *  @Description：获取分组器版本
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getGroupVerCode(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getGroupVerCode'+ #groupScheme + #dateFrom +#dateTo")
	public String getGroupVerCode(String groupScheme, String dateFrom, String dateTo) {
		String sql="SELECT distinct p.ver_code FROM drgs.mr_ipinfo ip \r\n" + 
				"left join drgs.t_patientdrg p on ip.pmainindex=p.parid\r\n" + 
				"where to_char(ip.p00290000,'yyyy-mm-dd') between '"+dateFrom+"'  and '"+dateTo+"'   \r\n" + 
				"and p.ver_code is not null and p.ver_region = '"+groupScheme+"'  and rownum=1 ";		
		return jdbcTemplateObj.queryForObject(sql,String.class);
	}
	/**
	 *  @Description：获取标杆年份
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getStdYear(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getGroupVerCode'+ #stdType + #verCode +#year")
	public String getStdYear(String stdType, String verCode, String year) {
		String sql="SELECT max(std.as_year) FROM drgs.t_annualstd std \r\n" + 
				"where std.as_year='"+year+"' and std.ver_code='"+verCode+"' and std.as_std_type='"+stdType+"' " ;
		String stdYear=jdbcTemplateObj.queryForObject(sql,String.class);
		// 当年不为空则取当年，没有取最新
		if(stdYear==null) {
			sql="SELECT max(std.as_year) FROM drgs.t_annualstd std \r\n" + 
				"where  std.ver_code='"+verCode+"' and std.as_std_type='"+stdType+"' " ;
			stdYear=jdbcTemplateObj.queryForObject(sql,String.class);
			return stdYear;
		}else {
			return stdYear;
		}
	  }
	/**
	 * 
	 *  @Description：获 取分组方案配置
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getConfigValueByCode(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getConfigValueByCode'+ #cfgCode")
	public String getConfigValueByCode(String cfgCode) {
		return jdbcTemplateObj.queryForObject("select cfg_value from drgs.T_CONFIG where cfg_code ='"+cfgCode+"'",String.class);	 
	 }
	/**
	 * 
	 *  @Description：监测当前分组方案以及监测时间范围内分组器版本号 
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#checkVerVersion(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'checkVerVersion' + #verRegion + #dateF +#dateT")
	public List<Map<String, Object>> checkVerVersion(String verRegion,String dateF,String dateT) {
		String sql="select  p.ver_code,count(1) from drgs.mr_ipinfo ip  \n"
				+ "left join drgs.t_patientdrg p \n"
				+ "on ip.pmainindex=p.parid \n"
				+ "where to_char(ip.P00290000,'yyyy-mm-dd')  between  '"+dateF+"'  and '"+dateT+"' \n "
				+ "and p.ver_region='"+verRegion+"' group by p.ver_code  order by count(1) desc ";
		return jdbcTemplateObj.queryForList(sql);
	}
	
	/**
	 *  @Description：根据院区获取科室组
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getDepGroupByHospCode(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getDepGroupByCode(String hospCode,String depGroupCode) {
		String sql="select distinct grup.depgroup_code code ,grup.depgroup_desc name, grup.DEPGROUP_ORDER  from drgs.t_depgroup grup \n"
				+ "  join drgs.t_department dep  on grup.depgroup_code=dep.pardepgroup_code \n"
				+ "where 1=1 \n";
		if(StringUtils.hasLength(hospCode)){
			sql+="and instr('"+hospCode+"',dep.parhosp_code) > 0 ";
		}
		if(StringUtils.hasLength(depGroupCode)){
			sql+="and grup.depgroup_code='"+depGroupCode+"' ";
		}
		sql+=" order by DEPGROUP_ORDER ";
		return jdbcTemplateObj.queryForList(sql);
	 }
	/**
	 *  @Description：根据医院编码获取病区
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getWardByHospCode(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getWardByCode(String hospCode,String depGroupCode, String depCode, String wardCode) {
		String sql="select w.ward_code code,w.ward_desc name from drgs.t_ward w  \n"
				+ " join drgs.t_department dep  on w.pardep_code=dep.dep_code \n"
			    + " where 1=1 \n";
		if(StringUtils.hasLength(hospCode)){
			sql+="and instr('"+hospCode+"',dep.parhosp_code) > 0 ";
		}
		if(StringUtils.hasLength(depGroupCode)){
			sql+="and w.pardepgroup_code='"+depGroupCode+"' ";
		}
		
		if(StringUtils.hasLength(depCode)){
			sql+="and w.pardep_code='"+depCode+"' ";
		}
		
		if(StringUtils.hasLength(wardCode)){
			sql+="and w.ward_code='"+wardCode+"' ";
		}
		sql+=" order by w.ward_order ";
		return jdbcTemplateObj.queryForList(sql);
	 }
	/**
	 * 
	 *  @Description：获取各级医师
	 *  @Author lhj 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getWardByCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String, Object>> getDoctor(IndexCal IndexCal) {
		String monitorType=IndexCal.getMonitorType();
		String dateFrom=DrgsIndex.formateDateFrom(IndexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(IndexCal.getDateTo());
		String hospCode=IndexCal.getHospCode();
		String sqlStr=" select x.group_code CCODE, x.group_name DDESC,LISTAGG(x.dep_ward,'+')  WITHIN GROUP (ORDER BY x.group_code , x.group_name) DEP  \r\n" + 
				"from ( \r\n" ; 
		if(IndexCal.getParwardCode()!=null && !"".equals(IndexCal.getParwardCode())){
			sqlStr+= "select distinct cg.group_code , cg.group_name, wa.ward_desc dep_ward  \r\n" ; 
		}else {
			sqlStr+= "select distinct cg.group_code , cg.group_name, a.dep_name dep_ward \r\n" ; 
		}
		sqlStr+="  from drgs.mr_baseinfo mrb \n "+
				"  left join  DRGs.MR_IPINFO t on mrb.pmainindex=t.pmainindex   \r\n" + 
				"  left join DRGs.t_Department a on t.P00300001=a.dep_code \r\n" + 
				"  left join drgs.t_ward wa on t.P00310001=wa.ward_code \r\n" ;
		   
		if("protomedicus".equals(monitorType)){
			sqlStr+=" left join drgs.t_clinicgroup cg on t.P00600201=cg.group_code   \r\n" ;
		}else if("clinic".equals(monitorType)){
			sqlStr+=" left join drgs.t_clinicgroup cg on t.P00601001=cg.group_code   \r\n" ;
		}else if("resident".equals(monitorType)){
			sqlStr+=" left join drgs.t_clinicgroup cg on t.P00600401=cg.group_code   \r\n" ;
		}else if("physician".equals(monitorType)){
			sqlStr+=" left join drgs.t_clinicgroup cg on t.P00600301=cg.group_code   \r\n" ;
		}
		sqlStr+=" where  to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \r\n" + 
				" and cg.group_code is not null   \r\n";
		if(StringUtils.hasLength(hospCode)){
			sqlStr+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		if(IndexCal.getPardepgroupCode()!=null && !"".equals(IndexCal.getPardepgroupCode())){
			sqlStr+=" and a.pardepgroup_code='"+IndexCal.getPardepgroupCode()+"' \r\n ";
		}
		if(IndexCal.getPardepCode()!=null && !"".equals(IndexCal.getPardepCode())){
			String deps=IndexCal.getPardepCode()==null?"":IndexCal.getPardepCode()+"";
			//科室角色下绩效分析下查看全部医师，包含,则说明包含多个科室
			if(deps.indexOf(",")!=-1){
				deps="'"+deps+"'";
				deps=deps.replaceAll(",", "','");
				sqlStr+=" and t.P00300001 in ("+deps+") \r\n ";
			}else{
				sqlStr+=" and t.P00300001='"+IndexCal.getPardepCode()+"' \r\n";
			}
		}
		if(IndexCal.getParwardCode()!=null && !"".equals(IndexCal.getParwardCode())){
			sqlStr+=" and t.P00310001='"+IndexCal.getParwardCode()+"' \r\n";
		}
		sqlStr+=" order by cg.GROUP_ORDER \r\n";
		sqlStr+="	)x group by x.group_code , x.group_name ";	
		return jdbcTemplateObj.queryForList(sqlStr);
	}
	@Override
	public List<Map<String, Object>> findCaseListGlobal(String filterStr) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select \n ");
		sql.append(" BASE.PMAININDEX, \n ");
		sql.append(" BASE.P00050000 MR_NO, \n ");
		sql.append(" BASE.P00060000 PATIENT_NAME, \n ");
		sql.append(" BASE.P00070001 SEX, \n ");
		sql.append(" BASE.P00090000 AGE_YEAR, \n ");
		sql.append(" BASE.P00090001 AGE_MONTH, \n ");
		sql.append(" BASE.P00090002 AGE_DAYS, \n ");
		sql.append(" TO_CHAR(IP.P00250000, 'YYYY-MM-DD') IN_DATE, \n ");
		sql.append(" TO_CHAR(IP.P00290000, 'YYYY-MM-DD') OUT_DATE, \n ");
		sql.append(" IP.P00300001 DEP_CODE, \n ");
		sql.append(" IP.P00300000 DEP_NAME, \n ");
		sql.append(" IP.P00320000 IN_DAYS, \n ");
		sql.append(" IP.P00800000 COSTAMOUNT, \n ");
		sql.append(" EXPENSESUM.DRUG_COST DRUG_COST, \n ");
		sql.append(" DIAG.PD0100 DIAGNOSE_DESC_MAIN \n ");
		sql.append(" FROM DRGS.MR_BASEINFO BASE \n ");
		sql.append(" LEFT JOIN DRGS.MR_IPINFO IP ON BASE.PMAININDEX=IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_DIAGNOSE DIAG ON BASE.PMAININDEX=DIAG.PMAININDEX AND DIAG.PDTYPE='M' \n ");
		sql.append(" LEFT JOIN DRGS.MR_OPERATION OPER ON BASE.PMAININDEX=OPER.PMAININDEX AND OPER.POTYPE='M' \n ");
		sql.append(" LEFT JOIN DRGS.MR_EXPENSE_SUM EXPENSESUM ON BASE.PMAININDEX=EXPENSESUM.PMAININDEX \n ");
		sql.append(" WHERE 1=1 \n ");
		sql.append(filterStr);
		return jdbcTemplateObj.queryForList(sql.toString());
	}
	
	@Override
	public void deleteCase(String parid) {
		StringBuilder sql = new StringBuilder();
		sql.append(" BEGIN \n ");
		sql.append("   DELETE FROM DRGS.MR_IPINFO T1 WHERE T1.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_APPENDPAGE T2 WHERE T2.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_DIAGNOSE T3 WHERE T3.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_OPERATION T4 WHERE T4.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_EXPENSE T5 WHERE T5.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_EXPENSE_SUM T6 WHERE T6.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_TRANSDEP T8 WHERE T8.PMAININDEX='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.T_PATIENTDRG T9 WHERE T9.PARID='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.T_GROUPINGLOG T10 WHERE T10.MR_ADM='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.T_OTHER_GROUPINGRESULT T11 WHERE T11.MR_ID='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.T_MEDINS T12 WHERE T12.PARID='"+parid+"'; \n ");
		sql.append("   DELETE FROM DRGS.MR_BASEINFO T13 WHERE T13.PMAININDEX='"+parid+"'; \n ");
		sql.append(" END; \n ");
		jdbcTemplateObj.execute(sql.toString());
	}
/**
 * 
 *  @Description：获取首页数据所有年月
 *  @Author liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#findMrTime()
 */
	@Override
	public List<Map<String, Object>> findMrTime() {
		return jdbcTemplateObj.queryForList("select distinct to_char(ip.P00290000,'yyyy-mm') mrtime from drgs.mr_ipinfo ip where ip.P00290000 is not null order by to_char(ip.P00290000,'yyyy-mm') desc");
	}
	/**
	 *  @Description：检测系统是否含有病组的绩效管理值
	 *  @Author：liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#isDrgManager()
	 */
	@Override
	public int isDrgManager() {
		return jdbcTemplateObj.queryForObject("SELECT count(1) FROM DRGS.t_Quotamanage t WHERE t.pardep_code is null and t.pardepgroup_code is null and t.par_ms_code='manager_inner' and t.pardrg_code is not null and t.parquota_code is not null", Integer.class);
	}

	/**
	 * @Description: 执行查询，返回Integer
	 * @param
	 * @return
	 * @throws
	 * @author chenhanlin
	 * @date 2018/7/26 15:57
	 */
	public Integer executeQueryForInt(String sql,Map<String,Object> condition){
		return jdbcTempSeverice.queryForObject(sql,condition,Integer.class) ;
	}

	/**
	 * @param sql
	 * @param list
	 * @return
	 * @throws
	 * @Description: 批量执行更新方法
	 * @author chenhanlin
	 * @date 2018/7/29 10:46
	 */
	@Override
	public int[] batchUpdate(String sql, List list) {
		//批量转数组
		SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
		int[] count = jdbcTempSeverice.batchUpdate(sql, beanSources);
		return count;
	}

    /**
     * 执行更新sql
     *
     * @param sql
     * @param condition
     * @return
     * @throws
     * @Description:
     * @author chenhanlin
     * @date 2018/7/30 14:24
     */
    @Override
    public int excuteUpdate(String sql, Map<String, Object> condition) {
        return jdbcTempSeverice.update(sql,condition) ;
    }

	/**
	 * @param sql
	 * @param condition
	 * @return
	 * @throws
	 * @Description: 执行查询的sql,返回List<T>
	 * @author chenhanlin
	 * @date 2018/8/6 16:26
	 */
	@Override
	public List excuteQueryForList(String sql, Map<String, Object> condition) {
		List resultList = null;
		try {
			resultList = jdbcTempSeverice.queryForList(sql,condition);
		} catch (DataAccessException e) {
			resultList = new ArrayList() ;
			e.printStackTrace();
		}
		return resultList ;
	}


	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'findSQLAnalysisreport' + #chaper + #section + #codeSql + #templateCode")
	public String findSQLAnalysisreport(String codeSql,String templateCode,String chaper,String section) {
		String sqlStr = "";
		StringBuffer sqlb = new StringBuffer(" select ar.sql from drgs.T_ANALYSISREPORT ar where 1=1 \n ") ;
		if(StringUtils.hasLength(chaper)) {
			sqlb.append(" and chapter = '"+chaper+"' \n ") ;
		}
		if(StringUtils.hasLength(templateCode)) {
			sqlb.append("and code_template = '"+templateCode+"' \n ") ;
		}
		if(StringUtils.hasLength(section)) {
			sqlb.append(" and section = '"+section+"' \n ") ;
		}
		if(StringUtils.hasLength(codeSql)) {
			sqlb.append(" and code_sql = '"+codeSql+"' \n ") ;
		}
		List<Object> sqlList = getRepository().createSqlQuery(sqlb.toString()).list();
		if (sqlList.size() > 0) {
			Clob clob = (Clob) sqlList.get(0);
			try {
				sqlStr = DrgsIndex.ClobToString(clob);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sqlStr;
	}
	
	@Override
	public String getLatestPatientDate() {
		String sql = "select to_char(max(ipinfo.P00290000),'yyyy-mm-dd') from drgs.mr_ipinfo ipinfo";
		String result = jdbcTemplateObj.queryForObject(sql,String.class);
		return result;
	}

}
